package com.gitee.xiao.ai;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wang xiao
 * date 2024/7/10
 */
public class DataPreprocessing {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/ticketdb";
    private static final String USER = "username";
    private static final String PASS = "password";

    public static List<UserData> fetchData() {
        List<UserData> dataList = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(DB_URL, USER, PASS)) {
            String query = "SELECT user_id, travel_time, travel_route FROM user_history";
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                dataList.add(new UserData(resultSet.getInt("user_id"), resultSet.getString("travel_time"), resultSet.getString("travel_route")));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataList;
    }

    public static List<UserData> fetchDataByUserId(String userId) {
        List<UserData> dataList = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(DB_URL, USER, PASS)) {
            String query = "SELECT user_id, travel_time, travel_route FROM user_history where user_id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, userId);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                dataList.add(new UserData(resultSet.getInt("user_id"), resultSet.getString("travel_time"), resultSet.getString("travel_route")));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataList;
    }
}
